set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=20;
set hive.exec.max.dynamic.partitions.pernode=20;

-----延迟天数计算-----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
with delay_detail as
         (
             SELECT
                 waybill_no
                  ,sign_network_code
                  ,sign_network_name
                  ,pres_sign_time
                  ,plan_sign_date
                  ,create_time
                  ,postpone_type
                  ,postpone_name
                  ,all_problem_date
                  ,sum(
                     case when postpone_type=2 and all_problem_date is not null and create_time<=all_problem_date then 1
                          when postpone_type=2 and all_problem_date is null and create_time<=pres_sign_time2 then 1
                          else 0  end
                 ) over(partition by waybill_no,sign_network_code,dt) as day_problem_cnt
                 ,case when all_problem_date is  null then datediff(pres_sign_time2,plan_sign_date)+1
                       when all_problem_date is not null  then datediff(all_problem_date,plan_sign_date)
                  end as diff
                  ,dt
             FROM
                 (
                     select
                         detail.waybill_no
                          ,detail.sign_network_code
                          ,detail.sign_network_name
                          ,detail.pres_sign_time
                          ,detail.plan_sign_date
                          ,problem.create_time
                          ,problem.postpone_type
                          ,problem.postpone_name
                          ,detail.dt
                          ,first_value(if(problem.postpone_type=3,problem.create_time,null)) over(partition by detail.waybill_no,detail.sign_network_code,detail.dt order by problem.postpone_type desc,problem.create_time ) as all_problem_date
                          ,detail.pres_sign_time2
                     from
                         (--未达成时效数据
                             select waybill_no
                                  ,sign_network_code
                                  ,sign_network_name
                                  ,date(pres_sign_time) as pres_sign_time
                                  ,date_sub(date(pres_sign_time),1) as pres_sign_time2
                             ,date(plan_sign_date) as plan_sign_date
                             ,dt
                             from jms_dm.dm_prescription_reach_details_sign_dt
                             where dt = '{{ execution_date | cst_ds }}'
                               and is_pres_standard=0 and is_pres_sign =1 and aging_sign_count_type=0
                               and not (datediff(pres_sign_time, plan_sign_date) = 1 and hour(pres_sign_time) < 5)
                               and extra_time is null --剔除乡镇加时的数据
                 )detail left join
                 ( --登记问题件
                     select waybill_no
                          ,registration_network_code
                          ,second_level_type_code
                          ,postpone_type
                          ,postpone_name
                          ,create_time
                     from
                         (
                             select
                                 a.waybill_no
                                  ,a.registration_network_code
                                  ,a.second_level_type_code
                                  ,date(a.scan_time) as create_time
                        ,b.postpone_type
                        ,case when b.postpone_type=1 then '不顺延'
                              when b.postpone_type=2 then '当天有效'
                              when b.postpone_type=3 then '全部有效'
                         end as  postpone_name
                         --去重优先取全部有效然后当天有效
                        ,row_number() over(partition by a.waybill_no ,a.registration_network_code ,date(a.scan_time) order by b.postpone_type desc,a.scan_time desc) as rnk
                                 from jms_dwd.dwd_sqs_registration_problem_piece_base_dt a
                                     inner join jms_dim.dim_problem_piece_type_base  b  on a.second_level_type_code  =b.code and b.is_enable=1 and b.postpone_type!=1
                                 where a.dt between date_add('{{ execution_date | cst_ds }}',-13) and '{{ execution_date | cst_ds }}'
                        )c where rnk=1
         ) problem on detail.waybill_no=problem.waybill_no and detail.sign_network_code=problem.registration_network_code and problem.create_time <= detail.pres_sign_time2
)t    )

insert overwrite table jms_tmp.tmp_already_sign_no_reach_delay_details_dt
select
    waybill_no            --运单号
     ,sign_network_code   --签收网点
     ,sign_network_name   --签收网点name
     ,pres_sign_time      --时效签收日期
     ,plan_sign_date      --规划签收日期
     ,create_time    as  problem_create_time     --问题件登记日期
     ,postpone_type  as  problem_postpone_type   --问题件类型1 无效问题件 2 当日有效问题件 3 全部有效问题件
     ,postpone_name  as problem_postpone_name    --问题件类型对应中文释义
     ,all_problem_date  as postpone_3_date       --全部有效问题件最早登记时间
     ,day_problem_cnt   as postpone_2_cnt        --当日有效问题件在最早登记时间之前的占天数或者在时效签收日期前天数
     ,diff              as postpone3_plan_diff   --无全部有效问题件=时效签收日期-规划签收日期+1 ；有全部有效问题件 = postpone_3_date-规划签收日期
     ,diff-day_problem_cnt as delay_days         --延误天数
     ,dt
from delay_detail
distribute by dt,pmod(hash(rand()),2);

----------------------------------------------------------------------------------------------------------------------------
with refund as (select waybill_no, print_time
                from jms_dwd.dwd_tab_reback_transfer_express_base_dt
                where dt between date_add('{{ execution_date | cst_ds }}', -15 + 1) and '{{ execution_date | cst_ds }}'
                  and print_flag = '1' --面单是否已打印 1是 0否
                group by waybill_no, print_time)

insert
overwrite
table
jms_dm.dm_already_sign_no_reach_details_dt
partition
(
dt
)
select waybill_no,
       sign_network_code,
       sign_network_name,
       pres_sign_time,
       plan_sign_date,
       delay_day,
       island,
       taking_time,
       sign_network_id,
       sign_subordinate_agent_id,
       sign_subordinate_agent_code,
       sign_subordinate_agent_name,
       sign_network_type,
       start_pick_network_id,
       start_pick_network_code,
       start_pick_network_name,
       start_subordinate_agent_id,
       start_subordinate_agent_code,
       start_subordinate_agent_name,
       start_sign_network_type,
       date_time,
       is_tyd,
       is_ntzk,
       dt
from (select a.waybill_no,
             a.sign_network_code,
             a.sign_network_name,
             a.pres_sign_time,
             a.plan_sign_date,
             (delay.delay_days - nvl(event.defer_day, 0)) as                             delay_day,
             a.island,--订单来源
             a.taking_time,--揽收时间
             net1.id                                      as                             sign_network_id, --签收网点id
             net1.agent_id                                as                             sign_subordinate_agent_id, --签收网点所属代理区
             a.sign_subordinate_agent_code, --签收代理区code
             a.sign_subordinate_agent_name, --签收代理区name
             net1.network_type                            as                             sign_network_type,--签收网点类型
             net2.id                                      as                             start_pick_network_id, --始发点id
             a.start_pick_network_code, --始发网点code
             a.start_pick_network_name, --始发网点name
             net2.agent_id                                as                             start_subordinate_agent_id, --始发代理区id
             a.start_subordinate_agent_code, --始发代理区code
             a.start_subordinate_agent_name, --始发代理区name
             net2.network_type                            as                             start_sign_network_type,--始发网点类型
             '{{ execution_date | cst_ds }}'              as                             date_time,
             '{{ execution_date | cst_ds }}'              as                             dt,
             row_number() over (partition by a.waybill_no order by event.defer_day desc) rn,
             if(waybill.waybill_no is not null,1,0) is_tyd,
             if(cutsomer.code is not null,1,0) is_ntzk
      from jms_dm.dm_prescription_reach_details_sign_dt a
           left join (
               select waybill_no from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
               where dt between date_add('{{ execution_date | cst_ds }}', -15) and '{{ execution_date | cst_ds }}'
               and express_type_code = 'TYD'
           ) waybill
           on waybill.waybill_no  = a.waybill_no
           left join
           (
                select code,name from jms_dim.dim_lmdm_sys_customer_addition
                where is_enable = 1 and is_delete = 1 and customer_category = 2
           ) cutsomer
           on cutsomer.code = a.customer_code
               left semi
               join
           jms_dm.dm_whole_decide_duty_pres_base_dt b
           on a.waybill_no = b.waybill_no
               and b.ziyou_duty_type in ('签收晚点')
               and b.platform_effect = b.ziyou_platform_effect
               and b.dt = '{{ execution_date | cst_ds }}'
               left anti join
               (
                    select waybill_id from (
                         select
                              a.waybill_id
                              ,a.field_name  as order_mark_en  -- 订单标签英文
                              ,b.value       as order_mark_zh  -- 订单标签中文
                         from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt a
                         left join jms_dim.dim_yl_oms_system_dict_base b on a. field_name = b.label and b.is_delete = 0
                         where a.dt between date_add('{{ execution_date | cst_ds }}', -15) and '{{ execution_date | cst_ds }}'
                         and b.value = '桃花岛逆向上门取件'
                    ) detail group by waybill_id
               ) order_field
           on order_field.waybill_id = a.waybill_no
           left anti join
               (
                   select waybill_no from (
                   select
                         a.ID,b.reply_content,a.waybill_no
                         from(
                         select
                         ID,waybill_no
                         from  jms_dwd.dwd_sqs_registration_problem_piece_base_dt
                         where dt between date_add('{{ execution_date | cst_ds }}', -15) and '{{ execution_date | cst_ds }}'
                         ) a
                         left join (
                         select
                         problem_piece_id,reply_content
                         from jms_dwd.dwd_problem_service_record_base_dt
                         where dt between date_add('{{ execution_date | cst_ds }}', -15) and '{{ execution_date | cst_ds }}'
                         ) b on a.ID = b.problem_piece_id
                         where b.problem_piece_id is not null
                         and b.reply_content = '暂放3天。'
                   ) detail group by waybill_no
               ) problem_desc
           on problem_desc.waybill_no = a.waybill_no
               left anti
               join refund on refund.waybill_no = a.waybill_no
               left anti
               join jms_dwd.dwd_tab_barscan_transfer_mail_base_dt mail
               on mail.dt between date_add('{{ execution_date | cst_ds }}', -10) and '{{ execution_date | cst_ds }}'
               and mail.BILL_CODE = a.waybill_no
               left join ( --延迟天数
          select waybill_no, sign_network_code, delay_days, dt
          from jms_tmp.tmp_already_sign_no_reach_delay_details_dt --此表粒度是运单号、签收网点、问题件登记日期
          where dt = '{{ execution_date | cst_ds }}'
          group by waybill_no, sign_network_code, delay_days, dt) delay
                         on delay.waybill_no = a.waybill_no and delay.sign_network_code = a.sign_network_code --网点维表
               left join jms_dim.dim_network_whole_massage net1 on a.sign_network_code = net1.code
               left join jms_dim.dim_network_whole_massage net2 on a.start_pick_network_code = net2.code
               left join (--剔除异常事件登记数据
          select involved_org_code    as involved_org_code --涉事组织编码
               , involved_org_name    as involved_org_name --涉事组织名称
               , defer_day            as defer_day         --上报时间
               , to_date(happen_time) as happen_time       --发生时间
               , to_date(end_time)    as end_time          --结束时间
--     ,row_number() over(partition by involved_org_code order by defer_day desc) as rk
          from jms_dim.dim_abnormal_event_report_base --异常事件报表
          where to_date(happen_time) between date_sub('{{ execution_date | cst_ds }}', 9) and '{{ execution_date | cst_ds }}') event
                         on a.sign_network_code = event.involved_org_code
                             and date(a.plan_sign_date) between happen_time and end_time
--          and event.rk=1
               left anti
               join ( --剔除仲裁派件网点为白名单的数据
          select network_code, network_name
            from (select if(detail.network_code = net.agent_code, net.code, detail.network_code) network_code,
                         if(detail.network_code = net.agent_code, net.name, detail.network_name) network_name,
                         detail.network_code                                                     network_code_origin,
                         detail.network_name                                                     network_name_origin
                  from (select get_json_object(json, '$.networkCode') network_code,
                               get_json_object(json, '$.networkName') network_name
                        from jms_dim.dim_arbitration_configuration_base
                                 LATERAL VIEW explode(split(
                                         regexp_replace(
                                                 regexp_replace(network_info, '\\[|\\]', ''), '\\}\\, *\\{', '\\}\\;\\{'),
                                         '\\;')) jsons AS json
                        where '{{ execution_date | cst_ds }}' between start_date and end_date
                          and is_enable = 1
                          and configuration_type = 1) detail
                           left join
                       jms_dim.dim_network_whole_massage net
                       on (detail.network_code = net.code or detail.network_code = net.agent_code)
                           and net.network_type = 6) detail
            group by network_code, network_name
            ) arbit on arbit.network_code = a.sign_network_code
               left anti
               join ( --剔除掉节假日平台大促数据1
          select get_json_object(network_info, '$.[0].networkCode') as network_code
               , get_json_object(network_info, '$.[0].networkName') as network_name
               , start_date
               , end_date
               , arbitration_first_type
          from jms_dim.dim_arbitration_configuration_base
          where is_enable = 1
            and configuration_type = 0
            and (get_json_object(arbitration_first_type, '$.[0].code') = '15' or
                 get_json_object(arbitration_first_type, '$.[1].code') = '15')) arbit2
                    on arbit2.network_code = a.sign_network_code
                        and date(a.taking_time) between arbit2.start_date and arbit2.end_date
               left anti
               join ( --剔除掉节假日平台大促数据2
          select get_json_object(network_info, '$.[0].networkCode') as network_code
               , get_json_object(network_info, '$.[0].networkName') as network_name
               , start_date
               , end_date
               , arbitration_first_type
          from jms_dim.dim_arbitration_configuration_base
          where is_enable = 1
            and configuration_type = 0
            and (get_json_object(arbitration_first_type, '$.[0].code') != '15' or
                 get_json_object(arbitration_first_type, '$.[1].code') != '15')) arbit3
                    on arbit3.network_code = a.sign_network_code
               left anti
               join ( --剔除筛单为网点和三段码类型的数据
          select network_code
          from jms_dim.dim_yl_oms_order_interceptor_base_dt
          where dt='{{ execution_date | cst_ds }}'
            and data_type in ('network', 'the_third_segment_code')
            and is_enable = 1
            and is_delete = 0
            and ((effect_time_type = 1
              and (date(effect_time_start) <= '{{ execution_date | cst_ds }}' and
                   date(effect_time_end) >= '{{ execution_date | cst_ds }}'))
              or effect_time_type = 0)
          group by network_code) inter1 on inter1.network_code = a.sign_network_code
               left anti
               join ( --剔除筛单数据为区域类型
          select province, null as city, null as area, 1 as area_type
          from jms_dim.dim_yl_oms_order_interceptor_base_dt
          where dt='{{ execution_date | cst_ds }}'
            and data_type = 'area'
            and is_enable = 1
            and is_delete = 0
            and ((effect_time_type = 1
              and (date(effect_time_start) <= '{{ execution_date | cst_ds }}' and
                   date(effect_time_end) >= '{{ execution_date | cst_ds }}'))
              or effect_time_type = 0)
            and province is not null
            and city is null
            and area is null
          group by province

          union all

          select province, city, null as area, 2 as area_type
          from jms_dim.dim_yl_oms_order_interceptor_base_dt
          where dt='{{ execution_date | cst_ds }}'
            and data_type = 'area'
            and is_enable = 1
            and is_delete = 0
            and ((effect_time_type = 1
              and (date(effect_time_start) <= '{{ execution_date | cst_ds }}' and
                   date(effect_time_end) >= '{{ execution_date | cst_ds }}'))
              or effect_time_type = 0)
            and province is not null
            and city is not null
            and area is null
          group by province, city

          union all

          select province, city, area, 3 as area_type
          from jms_dim.dim_yl_oms_order_interceptor_base_dt
          where dt='{{ execution_date | cst_ds }}'
            and data_type = 'area'
            and is_enable = 1
            and is_delete = 0
            and ((effect_time_type = 1
              and (date(effect_time_start) <= '{{ execution_date | cst_ds }}' and
                   date(effect_time_end) >= '{{ execution_date | cst_ds }}'))
              or effect_time_type = 0)
            and province is not null
            and city is not null
            and area is not null
          group by province, city, area) inter3 on (inter3.province = a.sign_provider_desc and inter3.area_type = 1)
          or (inter3.province = a.sign_provider_desc and inter3.city = a.sign_city_desc and inter3.area_type = 2)
          or (inter3.province = a.sign_provider_desc and inter3.city = a.sign_city_desc and
              inter3.area = a.sign_area_desc and
              inter3.area_type = 3)
               left anti
               join (select waybill_no
                     from jms_dm.dm_already_sign_no_reach_details_dt
                     where dt between date_add('{{ execution_date | cst_ds }}', -10) and date_add('{{ execution_date | cst_ds }}', -1)
                     group by waybill_no) yest
                    on a.waybill_no = yest.waybill_no
      where a.dt = '{{ execution_date | cst_ds }}'
        and a.is_pres_standard = 0
        and a.is_pres_sign = 1
        and a.aging_sign_count_type = 0
        and not (datediff(a.pres_sign_time, a.plan_sign_date) = 1 and hour(a.pres_sign_time) < 5)
        and a.extra_time is null --剔除乡镇加时为null的数据
        and net1.is_suspend != 1
        and net1.is_enable = 1   --剔除暂停、禁用网点
        and  datediff(a.dt,date(nvl(net1.opening_time,'1999-01-01 00:00:00'))) > 15  --剔除新开网点小于15天
--        and (delay.delay_days - nvl(event.defer_day, 0)) > 0 --剔除延迟天数=0的单号
     ) detail
where rn = 1
  and delay_day > 0
    distribute by dt
    , pmod(hash(rand()), 2);

--------------------------------------------------------------------------
---汇总--------------------------------------------------------------------
insert overwrite table jms_dm.dm_already_sign_no_reach_summary_dt
select
      sign_network_code                   --责任主体编码
     ,sign_network_name                   --责任主体名称
     ,sign_subordinate_agent_code         --责任代理区编码
     ,sign_subordinate_agent_name         --责任代理区名称
     ,count(1) as delay_cnt               --延误总票数(票)
     ,count(case when delay_day = 1 then waybill_no end) as delay_day1_cnt         --延误1天(票)
     ,count(case when delay_day = 2 then waybill_no end) as delay_day2_cnt         --延误2天(票)
     ,count(case when delay_day = 3 then waybill_no end) as delay_day3_cnt         --延误3天(票)
     ,count(case when delay_day = 4 then waybill_no end) as delay_day4_cnt         --延误4天(票)
     ,count(case when delay_day = 5 then waybill_no end) as delay_day5_cnt         --延误5天(票)
     ,count(case when delay_day = 6 then waybill_no end) as delay_day6_cnt         --延误6天(票)
     ,count(case when delay_day = 7 then waybill_no end) as delay_day7_cnt         --延误7天(票)
     ,count(case when delay_day > 7 then waybill_no end) as delay_day7_plus_cnt    --延误7+天(票)
     ,dt as date_time    --查询日期，时效签收日期
     ,dt
from jms_dm.dm_already_sign_no_reach_details_dt
where dt ='{{ execution_date | cst_ds }}'
group by sign_network_code                   --责任主体编码
       ,sign_network_name                   --责任主体名称
       ,sign_subordinate_agent_code         --责任代理区编码
       ,sign_subordinate_agent_name         --责任代理区名称
       ,dt
    distribute by 1;